﻿using System;
using System.Configuration;
using System.Data;
using Oracle.DataAccess.Client;
using System.Collections.Generic;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    /// <summary>
    /// 数据库通用操作类
    /// </summary>
    public class OracleDataAccess : IDisposable
    {
        private readonly static string connstr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
        //private readonly static string connstr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.143.198)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=FJYC)));User Id=ADMIN;Password=asb#1234;";
        private OracleConnection conn;
        public OracleDataAccess()
            : this(connstr)
        {
        }
        /// <summary>
        /// 带连接字符串的构造函数
        /// </summary>
        /// <param name="constr"></param>
        public OracleDataAccess(string constr)
        {
            conn = new OracleConnection(constr);
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void Open()
        {
            //打开数据库连接
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                try
                {
                    if (conn.State == ConnectionState.Broken)
                    {
                        //先关闭，然后打开数据库连接
                        conn.Close();
                    }
                    //打开数据库连接
                    conn.Open();
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void Close()
        {
            //判断连接的状态是否已经打开
            if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Broken)
            {
                conn.Close();
            }
        }
        /// <summary>  
        /// 执行查询语句，并返回查询结果集中第一行的第一列（object类型）。如果找不到结果集中第一行的第一列，则返回 null 引用
        /// </summary>  
        /// <param name="sql">SQL查询语句</param>  
        /// <returns></returns>  
        public object ExecuteScalar(string sql, IList<OracleParameter> cmdParms)
        {
            Open();
            OracleCommand cmd = new OracleCommand();
            this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);

            return cmd.ExecuteScalar();
        }
        /// <summary>  
        /// 执行查询语句，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )  
        /// </summary>  
        /// <param name="sql">SQL查询语句</param>  
        /// <returns></returns>  
        public OracleDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }
        /// <summary>  
        /// 执行查询语句，返回 OracleDataReader ( 注意：调用该方法后，一定要对OracleDataReader进行Close )  
        /// </summary>  
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns> 
        public OracleDataReader ExecuteReader(string sql, IList<OracleParameter> cmdParms)
        {
            Open();
            OracleCommand cmd = new OracleCommand();
            this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);
            return cmd.ExecuteReader();
        }
        /// <summary>  
        /// 执行不带参数的SQL语句 
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteCommand(string sql)
        {
            return ExecuteCommand(sql, null);
        }
        /// <summary>  
        /// 使用事务执行不带参数的SQL语句 
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteCommandWithTransaction(string sql)
        {
            return ExecuteCommandWithTransaction(sql, null);
        }
        /// <summary>  
        /// 执行SQL语句，返回影响的记录数  
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响的记录数</returns>  
        public int ExecuteCommand(string sql, IList<OracleParameter> cmdParms)
        {
            Open();
            OracleCommand cmd = new OracleCommand();
            try
            {
                this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);

                return cmd.ExecuteNonQuery();
            }
            catch (OracleException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 执行sql语句，返回OracleCommand对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public OracleCommand ExecuteCommandReturnCommand(string sql, IList<OracleParameter> cmdParms)
        {
            Open();
            OracleCommand cmd = new OracleCommand();
            try
            {
                this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);

                cmd.ExecuteNonQuery();
                return cmd;
            }
            catch (OracleException e)
            {
                throw e;
            }
        }
        /// <summary>  
        /// 使用事务执行SQL语句，返回影响的记录数
        /// </summary>  
        /// <param name="sql">SQL命令语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响的记录数</returns>  
        public int ExecuteCommandWithTransaction(string sql, IList<OracleParameter> cmdParms)
        {
            Open();
            OracleCommand cmd = new OracleCommand();
            using (OracleTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    PrepareCommand(cmd, conn, trans, CommandType.Text, sql, cmdParms);
                    int rowsAffected = cmd.ExecuteNonQuery();
                    trans.Commit();//提交事务
                    return rowsAffected;
                }
                catch (OracleException e)
                {
                    trans.Rollback(); //事务回滚
                    throw e;
                }
            }
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataSet 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataSet ExecuteFillToDataSet(string sql)
        {
            return ExecuteFillToDataSet(sql, null);
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataSet 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public DataSet ExecuteFillToDataSet(string sql, IList<OracleParameter> cmdParms)
        {
            Open();
            OracleCommand cmd = new OracleCommand();
            this.PrepareCommand(cmd, conn, null, CommandType.Text, sql, cmdParms);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataTable 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataTable ExecuteFillToDataTable(string sql)
        {
            return ExecuteFillToDataSet(sql).Tables[0];
        }
        /// <summary>
        /// 执行SQL查询语句，返回数据到 DataTable 中
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns></returns>
        public DataTable ExecuteFillToDataTable(string sql, IList<OracleParameter> cmdParms)
        {
            return ExecuteFillToDataSet(sql, cmdParms).Tables[0];
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input || parm.Direction == ParameterDirection.ReturnValue) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (parm.OracleDbType != OracleDbType.Clob && (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2))
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }
        /// <summary>
        /// 实现IDisposable
        /// </summary>
        public void Dispose()
        {
            this.Close();
        }
        /// <summary>
        /// 析构函数
        /// </summary>
        ~OracleDataAccess()
        {
            this.Close();
        }

        public OracleTransaction BeginTransaction()
        {
            Open();
            return conn.BeginTransaction();
        }

        public DataTable QuickPage(string TbName, string FieldStr, string RowFilter, string SortStr, int MinRowNum, int MaxRowNum, ref int RecordCount)
        {
            OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings["OracleConnstr"].ToString());
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "MaterialManage.Per_QuickPage";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("TbName", OracleDbType.NVarchar2, 50);    //表  名
            cmd.Parameters["TbName"].Direction = ParameterDirection.Input;
            cmd.Parameters["TbName"].Value = TbName;

            cmd.Parameters.Add("FieldStr", OracleDbType.NVarchar2, 3000);   //字段集
            cmd.Parameters["FieldStr"].Direction = ParameterDirection.Input;
            cmd.Parameters["FieldStr"].Value = FieldStr;

            cmd.Parameters.Add("RowFilter", OracleDbType.NVarchar2, 3000);  //过滤条件
            cmd.Parameters["RowFilter"].Direction = ParameterDirection.Input;
            cmd.Parameters["RowFilter"].Value = RowFilter;

            cmd.Parameters.Add("SortStr", OracleDbType.NVarchar2, 3000);   //排序字段
            cmd.Parameters["SortStr"].Direction = ParameterDirection.Input;
            cmd.Parameters["SortStr"].Value = SortStr;

            cmd.Parameters.Add("MinRowNum", OracleDbType.Int32);    //分页小值
            cmd.Parameters["MinRowNum"].Direction = ParameterDirection.Input;
            cmd.Parameters["MinRowNum"].Value = MinRowNum;

            cmd.Parameters.Add("MaxRowNum", OracleDbType.Int32);    //分页大值
            cmd.Parameters["MaxRowNum"].Direction = ParameterDirection.Input;
            cmd.Parameters["MaxRowNum"].Value = MaxRowNum;

            cmd.Parameters.Add("TotalCount", OracleDbType.Int32);    //页总记录数
            cmd.Parameters["TotalCount"].Direction = ParameterDirection.Output;
            cmd.Parameters["TotalCount"].Value = 0;

            //cmd.Parameters.Add("Cur_ReturnCur", OracleDbType.Cursor);   //返回的游标
            //cmd.Parameters["Cur_ReturnCur"].Direction = ParameterDirection.Output;

            DataSet Ds = new DataSet();
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            adapter.Fill(Ds);
            conn.Close();

            //总记录数
            RecordCount = int.Parse(cmd.Parameters["TotalCount"].Value.ToString());
            return Ds.Tables[0];
        }

    }
}
